Performance considerations in one-to-many links
The information provided in this section is intended to help you maximize processing time and minimize network traffic when you are running your reports. You will learn about the best ways to use selection formulas and indexes in one-to-many situations to make your reporting more efficient. If you do not use the information in this section, your reports may end up processing dozens or even hundreds more records than necessary.
When a one-to-many situation exists between two database tables and the program matches up records from the tables, there are a number of factors that determine how many records the program reads and evaluates.
The tables that follow show the effects of the different factors on the number of records the program ultimately has to read. The charts are based on these assumptions:
- Table A contains 26 records (one for each letter in the alphabet).
- Table B contains 2600 records (100 matching records for every record in Table A).
- The scenario is to produce a report that finds two specific records in Table A and the 200 records (100+100) in Table B that match those two records in Table A.
In a best case scenario, the program would only have to read about 200 records to accomplish the task.
In a worst case scenario the program would have to read about 67,600 records to accomplish the same task.
Note: The performance considerations for data files are different from the considerations for SQL databases. A data file is any non-SQL database that is accessed directly from Crystal Reports. For the purpose of this discussion, an SQL database is any SQL database accessed directly from Crystal Reports or through ODBC as well as any other database types that are accessed through ODBC. For a better understanding of the difference between direct access databases and ODBC data sources, see Accessing Data Sources.
Extended descriptions of chart columns
The performance charts use the following columns:
- Linking or Subreport
Are you creating a report from linked databases or are you inserting a subreport and binding it to the data in your primary report?
- Selection Formula
Does your primary report include a record selection formula that sets range limits on the key (indexed) field in Table A?
- Index A
Is Table A on the field you are going to use indexed to match up the records?
- Index B
Is Table B on the field you are going to use indexed to match up the records?
- Reads A
How many records does the program have to read out of Table A to find the two records it is looking for?
- For each A reads in B
How many records does the program have to read in Table B to find the 200 records it is looking for?
- Total Records Read
What is the total number of records the program has to process to complete the task?
PC Data
|
Linking/
Subreport
| Selection
Formula
| Index A
| Index B
| Reads A
| For each
A reads
in B
| Total
Records
Read
|
Linking
| No
| Yes or No
| Yes
| 26
| 100 (26*100)
| 2600
|
Linking
| Yes
| No
| Yes
| 26
| 100 (26*100)
| 2600
|
Linking
| Yes
| Yes
| Yes
| 2
| 100 (2*100)
| 200
|
Subreport
| No
| No
| No
| 26
| 2600 (26*2600)
| 67,600
|
Subreport
| No
| Yes
| No
| 2
| 2600 (26*2600)
| 67,600
|
Subreport
| No
| Yes
| Yes
| 26
| 100 (26*100)
| 2600
|
Subreport
| Yes
| No
| No
| 2
| 2600 (2*2600)
| 5200
|
Subreport
| Yes
| No
| Yes
| 26
| 100 (26*100)
| 2600
|
Subreport
| Yes
| Yes
| Yes
| 2
| 100 (2*100)
| 200
|
SQL Data
|
Linking/
Subreport
| Selection
Formula
| Reads A
| For each A reads
in B
| Total Records
Read
|
Linking
| No
| 26
| 100 (26*100)
| 2600
|
Linking
| Yes
| 2
| 100 (2*100)
| 200
|
Subreport
| No
| 26
| 100 (26*100)
| 2600
|
Subreport
| Yes
| 2
| 100 (2*100)
| 200
|